drop table if exists spmi_tmp.dm_spmi_enter_port_weight_network_sum;
create external table spmi_tmp.dm_spmi_enter_port_weight_network_sum(
     entry_mark_mth                     string comment '入账月份'
    ,piece_financial_center_id          string comment '派件财务中心ID'
    ,piece_financial_center_code        string comment '派件财务中心代码'
    ,piece_financial_center_name        string comment '派件财务中心名称'
    ,receiver_province_id               string comment '收件省份ID	'
    ,receiver_province_name             string comment '收件省份名称'
    ,destination_id                     string comment '目的地ID'
    ,destination_code                   string comment '目的地代码'
    ,destination_name                   string comment '目的地名称'
    ,sign_network_code                  string comment '签收网点代码'
    ,piece_network_code                 string comment '派件网点代码'
    ,destination_weight_range1_cnt      bigint comment '目的地重量范围1数量, 0-0.5KG(含)件量'
    ,destination_weight_range2_cnt      bigint comment '目的地重量范围2数量, 0.5KG-1KG(含)件量'
    ,destination_weight_range3_cnt      bigint comment '目的地重量范围3数量, 1KG-3.2KG(含)件量'
    ,destination_weight_range4_cnt      bigint comment '目的地重量范围4数量, 3.2KG以上件量'
    ,destination_all_weight_range_cnt   bigint comment '目的地所有重量范围数量'
)COMMENT '进港公斤段统计件量占比报表-月维度'
STORED AS PARQUET
LOCATION 'hdfs://yl-bg-hdp/dw/hive/spmi_tmp.db/external/dm_spmi_enter_port_weight_network_sum'
;


drop table if exists spmi_dm.dm_spmi_enter_port_weight_range_rate_month;
create external table spmi_dm.dm_spmi_enter_port_weight_range_rate_month(
    id                            string comment 'ID'
    ,entry_mark_mth               string comment '入账月份'
    ,piece_financial_center_id    string comment '派件财务中心ID'
    ,piece_financial_center_code  string comment '派件财务中心代码'
    ,piece_financial_center_name  string comment '派件财务中心名称'
    ,receiver_province_id         string comment '收件省份ID	'
    ,receiver_province_name       string comment '收件省份名称'
    ,destination_id               string comment '目的地ID'
    ,destination_code             string comment '目的地代码'
    ,destination_name             string comment '目的地名称'
    ,sum_type                     string comment '汇总类型'
    ,all_weight_range_cnt         bigint comment '数量合计'
    ,all_weight_range_rate        decimal(16, 6)    comment '占比合计'
    ,weight_range1_cnt            bigint            comment '重量范围1数量, 0-0.5KG(含)件量'
    ,weight_range1_rate           decimal(16, 6)    comment '重量范围1占比, 0-0.5KG(含)件量'
    ,weight_range2_cnt            bigint            comment '重量范围2数量, 0.5KG-1KG(含)件量'
    ,weight_range2_rate           decimal(16, 6)    comment '重量范围2占比, 0.5KG-1KG(含)件量'
    ,weight_range3_cnt            bigint            comment '重量范围3数量, 1KG-3.2KG(含)件量'
    ,weight_range3_rate           decimal(16, 6)    comment '重量范围3占比, 1KG-3.2KG(含)件量'
    ,weight_range4_cnt            bigint            comment '重量范围4数量, 3.2KG以上件量'
    ,weight_range4_rate           decimal(16, 6)    comment '重量范围4占比, 3.2KG以上件量'
)COMMENT '进港公斤段统计件量占比报表-月维度'
PARTITIONED BY (mt string comment '入账月份')
STORED AS PARQUET
LOCATION 'hdfs://yl-bg-hdp/dw/hive/spmi_dm.db/external/dm_spmi_enter_port_weight_range_rate_month'
;

